
*** Load landkreis ids
use "${altered_data}landkreis_ids.dta", clear 

*** Universe of Cells

gen y = 2021

expand 12
bys idlandkreis y: gen m = _n

expand 31
bys idlandkreis y m: gen d = _n

gen date = mdy(m,d,y)
format date %td

drop m d y

keep if date!=.

expand 6
bys idlandkreis date: gen n = _n

gen str altersgruppe = ""
replace altersgruppe = "A00-A04" 	if n==1
replace altersgruppe = "A05-A14" 	if n==2
replace altersgruppe = "A15-A34" 	if n==3
replace altersgruppe = "A35-A59" 	if n==4
replace altersgruppe = "A60-A79" 	if n==5
replace altersgruppe = "A80+" 		if n==6

drop n

expand 2
bys idlandkreis date altersgruppe: gen female = _n-1

gen str geschlecht = "M" if female==0
replace geschlecht = "W" if female==1

* age groups
gen age 	= 0 if inlist(altersgruppe,"A00-A04")
replace age = 1 if inlist(altersgruppe,"A05-A14")
replace age = 2 if inlist(altersgruppe,"A15-A34")
replace age = 3 if inlist(altersgruppe,"A35-A59")
replace age = 4 if inlist(altersgruppe,"A60-A79", "A80+")

lab def agelab 0 "0-4" 1 "5-14" 2 "15-34" 3 "35-59" 4 "60+", replace
lab val age agelab



*** sample period

keep if inrange(date,td(01feb2021),td(02nov2021))



*** Generate data sets

levelsof date, local(dates) clean

	
		* Merge RKI
		gen meldedatum2 = date

		merge 1:1 idlandkreis meldedatum2 altersgruppe geschlecht using "${altered_data}kreis_age_gender_daily_meldedatum.dta", keep(master match) keepusing(cases casualties) nogen
		
		rename cases 		cases_melde
		rename casualties 	casualties_melde
		
		drop meldedatum2
		
		* Group young and old together and aggregate Berlin data
		replace idlandkreis = 11000 if idlandkreis > 11000 & idlandkreis < 12000
		collapse (sum) cases* casualties* (lastnm) idbundesland bundesland, by(idlandkreis date age female)
		
		* Generate additional variables:
		recode cases* casualties* (. = 0)
		
		gen cases_melde_agg = 0	
		
		foreach d of local dates {	
			bys idlandkreis age female (date): replace cases_melde_agg = cases_melde_agg[_n-1] + cases_melde if date==`d' & _n>1	
		}


		bys idlandkreis age female (date): gen cases_melde_agg14 = cases_melde_agg[_n-14]
			
		recode cases_melde_agg14 (. = 0)

		bys idlandkreis date: egen cases_melde_agg_all 	= total(cases_melde_agg)
		bys idlandkreis date: egen cases_melde_agg14_all = total(cases_melde_agg14)
		
		rename casualties_melde deaths_melde
		
	
		gen month = month(date)
		
		
		*** Merge Regional Data


		* Merge corresponding population: age and gender
		merge m:1 idlandkreis age female using "${regionalstatistik_out}kreis_pop_age_gender_Destatis.dta", keep(master match) nogen
		
				
		* Group male and female together
		collapse (sum) cases* deaths* pop (lastnm) idbundesland bundesland, by(idlandkreis date age)
		
		* Cases / Deaths per Kreis in total
		tempfile pres
		save `pres'
		collapse (sum) cases_melde deaths_melde pop, by(idlandkreis date)
		
		foreach var of varlist cases_melde deaths_melde pop {
			rename `var' `var'_total
		}

		merge 1:m idlandkreis date using `pres', nogen
		
		* per 100K of corresponding population
		foreach var of varlist cases* deaths* {
			if substr("`var'", -6, 6) == "_total" {
				replace `var' = (`var') / (pop_total / 100000)
				replace `var' = . if age > 0
			}
			else {
				replace `var' = (`var') / (pop / 100000)
			}
		}
		
		
		
		* Save Data
		rename idlandkreis RS
		order RS idbundesland bundesland date age 
		qui compress
		save "${altered_data}data_melde.dta", replace 
		




